﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Text;

namespace Dashboard.ProductBonus.Business
{
    public class BonusReport : BusinessBase
    {
        public DataTable GetDailyBonus(DateTime dateFrom, DateTime dateTo, string team, string staff, int workingDays)
        {
            string sql = GetBonusSql(dateFrom, dateTo, team, staff, workingDays) + "  order by dept,team,shift,staffno,workingdate";
            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetSummaryBonus(DateTime dateFrom, DateTime dateTo, string team, string staff, int days)
        {
            string sql = @"
            select staffno as StaffNo,Name,dept as Dept,team as Team,shift as Shift,WorkType,basicbonus as BasicBonus,
            sum(Attendance) as Attendance,sum(WorkingHour) as WorkingHour,
            sum(AssistanceHour) as AssitanceHour, sum(TotalHours) as TotalHours,MAX(workingdays) as WorkingDays,sum(DailyBonus) as MonthlyBonus 
            from ({0}) m group by staffno,name,dept,team,shift,WorkType,basicbonus
             order by dept,team,shift,staffno
            ";

            sql = string.Format(sql, GetBonusSql(dateFrom, dateTo, team, staff, days));

            return DbConnection.DataSet(sql).Tables[0];            
        }

        private string GetBonusSql(DateTime dateFrom, DateTime dateTo, string team, string staff, int workingDays)
        {

            string condition = string.Empty;
            if (!string.IsNullOrEmpty(team))
            {
                condition += " and c.team = '" + team + "'";
            }
            if (!string.IsNullOrEmpty(staff))
            {
                condition += " and a.staffno = '" + staff + "'";
            }    
            string assistantBonus = ConfigurationManager.AppSettings["AssistantBonus"];
            if (string.IsNullOrEmpty(assistantBonus))
                assistantBonus = "200.0";
             //StaffNo., Name, Dept, Team, Shift,WorkType, BasicBonus, WorkDate, AttendanceHr, WorkingHr, AssistantHr,  TotalHr, WorkDays, Daily Bonus

            string sql = @"
                    select StaffNo,Name,Dept,Team,Shift,WorkType,BasicBonus,WorkingDate,Attendance,WorkingHour,AssistanceHour,WorkingHour+AssistanceHour as TotalHours,
                    WorkingDays,CONVERT(int, z.bonus+z.assitentbonus) as DailyBonus from(
                    select y.staffno as StaffNo,y.staffname as Name,y.dept as Dept,y.team as Team,y.shift as Shift,WorkType,y.basicbonus as BasicBonus,y.workingdate,
                    y.Attendance,y.WorkingHour,y.assistanthour as AssistanceHour,y.workingdays as WorkingDays,
                    'bonus' = 
                    case 
	                    when y.Attendance= 0 or y.workingdays = 0 then 0
	                    when y.Workinghour / y.Attendance < 0.9 then 0
	                    else round((y.basicbonus*y.Workinghour/(y.workingdays*y.Attendance)),0)
                    end,
                    'assitentbonus' = 
                    case 
	                    when y.Attendance = 0 or y.workingdays = 0 then 0
	                    else round(({4}*y.assistanthour / (y.workingdays*y.Attendance)),0) 
                    end
                    from (
                    select x.staffno,x.staffname,x.dept,x.team,x.shift,x.basicbonus,x.workingdate, x.worktype,
         	        (x.ScheduleTime+x.OvertimeTime-x.AbsentTime) AS Attendance,x.workinghour,
                    case when x.worktype like N'%班长%' or x.worktype like N'%辅助%' then (x.ScheduleTime+x.OvertimeTime-x.AbsentTime)
                    else  x.AssistantTime end AS assistanthour, x.workingdays
                    from
                    (
                    select a.StaffNo,c.name as staffname,c.dept,c.team,c.worktype, c.shift,c.basicbonus1 as basicbonus,a.workingdate, isnull(b.totaltime,0) as ScheduleTime,
                    isnull(d.TotalTime,0) as AbsentTime,
                    isnull(e.TotalTime,0) as AssistantTime,
                    isnull(f.TotalTime,0) as OvertimeTime,
                    isnull(g.OEETime,0) as Workinghour,
                    {3} as workingdays
                    from WorkerShiftSchedule a 
                    left outer join WorkingShift b on a.workingShift=b.id
                    inner join namelist c on a.staffno=c.staffno
                    left outer join (select a.staffno,a.date,sum(a.TotalTime) as TotalTime  from AbnormalWorkingTime a inner join NonworkingReason n on a.reasoncode=n.pcode where n.pvalue3='Y' and a.timetype='UT' AND a.date BETWEEN '{0}' AND '{1}'  group by a.staffno,a.date)  d on a.staffno=d.staffno and a.workingdate=d.date
                    left outer join AbnormalWorkingTime e on a.staffno=e.staffno and a.workingdate=e.date and e.timetype='Assistant'
                    left outer join (select a.staffno,a.date,sum(a.TotalTime) as TotalTime from AbnormalWorkingTime a inner join NonworkingReason n on a.reasoncode=n.pcode where n.pvalue3='N' and a.timetype='UT' AND a.date BETWEEN '{0}' AND '{1}'  group by a.staffno,a.date)  f on a.staffno=f.staffno and a.workingdate=f.date
                    left outer join OEE_View g on a.staffno=g.staffno and a.workingdate=g.workingdate
                    where a.workingdate>='{0}' and a.workingdate<='{1}' {2}
                    ) x) y ) z 
            ";   
            return string.Format(sql, dateFrom.ToString("yyyy-MM-dd"), dateTo.ToString("yyyy-MM-dd"), condition, workingDays, assistantBonus);
        }
    }
}
